package com.sunnyday.excelexporttest;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.supercsv.io.CsvMapWriter;
import org.supercsv.io.ICsvMapWriter;
import org.supercsv.prefs.CsvPreference;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.util.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.commons.lang3.time.StopWatch;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;


@SpringBootApplication
public class ExcelExportTestApplication implements CommandLineRunner {
    private static final String OUTPUT_DIR = "./output/";

    public static void main(String[] args) {
        SpringApplication.run(ExcelExportTestApplication.class, args);
    }

    // 模拟数据：10万行，每行200列，每列10字符
    private List<Map<String, String>> generateData(int rows, int cols) {
        List<Map<String, String>> data = new ArrayList<>(rows);
        for (int i = 0; i < rows; i++) {
            Map<String, String> row = new LinkedHashMap<>(cols);
            for (int j = 0; j < cols; j++) {
                row.put("col" + j, "val" + j);
            }
            data.add(row);
        }
        return data;
    }

    @Override
    public void run(String... args) throws Exception {
        FileUtils.forceMkdir(new File(OUTPUT_DIR));
        int rows = 100000;
        int cols = 200;
        List<Map<String, String>> data = generateData(rows, cols);

        System.out.println("=========== Excel导出主流工具性能测试比较 ===========");

        // 1. EasyExcel 一次性写入
        writeWithEasyExcelOnce(data);

        // 2. EasyExcel 流式分批写入
        writeWithEasyExcelBatch(data, 1000);

        // 3. Apache POI SXSSF 流式写入
        writeWithPoiSXSSF(data);

        // 4. CSV 导出
        writeCSV(data);
    }

    private void writeWithEasyExcelOnce(List<Map<String, String>> data) {
        PerformanceMonitor monitor = new PerformanceMonitor();
        monitor.start();
        String fileName = OUTPUT_DIR + "easyexcel-once.xlsx";
        // 构建 head（List<List<String>> 形式）
        List<List<String>> head = new ArrayList<>();
        for (String key : data.get(0).keySet()) {
            head.add(Collections.singletonList(key)); // 每列一个标题
        }

        EasyExcel.write(fileName)
                .head(head)
                .sheet("Sheet1")
                .doWrite(data);
        monitor.end("EasyExcel 一次性写入");
    }

    private void writeWithEasyExcelBatch(List<Map<String, String>> data, int batchSize) {
        PerformanceMonitor monitor = new PerformanceMonitor();
        monitor.start();
        String fileName = OUTPUT_DIR + "easyexcel-batch.xlsx";
        // 构建 head
        List<List<String>> head = new ArrayList<>();
        for (String key : data.get(0).keySet()) {
            head.add(Collections.singletonList(key));
        }

        ExcelWriter writer = EasyExcel.write(fileName)
                .head(head)
                .build();
        WriteSheet sheet = EasyExcel.writerSheet("Sheet1").build();

        for (int i = 0; i < data.size(); i += batchSize) {
            int end = Math.min(i + batchSize, data.size());
            List<Map<String, String>> batch = data.subList(i, end);
            writer.write(batch, sheet);
        }
        writer.finish();
        monitor.end("EasyExcel 流式分批写入");
    }

    private void writeWithPoiSXSSF(List<Map<String, String>> data) throws Exception {
        PerformanceMonitor monitor = new PerformanceMonitor();
        monitor.start();
        String fileName = OUTPUT_DIR + "poi-sxssf.xlsx";
        Workbook wb = new SXSSFWorkbook(100);
        Sheet sheet = wb.createSheet("Sheet1");
        int rowIdx = 0;
        List<String> headers = new ArrayList<>(data.get(0).keySet());

        // Header
        Row headerRow = sheet.createRow(rowIdx++);
        for (int i = 0; i < headers.size(); i++) {
            headerRow.createCell(i).setCellValue(headers.get(i));
        }

        // Data
        for (Map<String, String> rowMap : data) {
            Row row = sheet.createRow(rowIdx++);
            for (int i = 0; i < headers.size(); i++) {
                row.createCell(i).setCellValue(rowMap.get(headers.get(i)));
            }
        }

        try (FileOutputStream fos = new FileOutputStream(new File(fileName))) {
            wb.write(fos);
        }
        ((SXSSFWorkbook) wb).dispose();
        monitor.end("POI SXSSF 流式写入");
    }

    private void writeCSV(List<Map<String, String>> data) throws Exception {
        PerformanceMonitor monitor = new PerformanceMonitor();
        monitor.start();
        String fileName = OUTPUT_DIR + "export.csv";
        try (ICsvMapWriter writer = new CsvMapWriter(new FileWriter(fileName), CsvPreference.STANDARD_PREFERENCE)) {
            String[] headers = data.get(0).keySet().toArray(new String[0]);
            writer.writeHeader(headers);
            for (Map<String, String> row : data) {
                writer.write(row, headers);
            }
        }
        monitor.end("CSV 导出");
    }

}
